﻿using System;
using System.Collections.Generic;
using System.Linq;
using SGQ.Model;
using System.Data.SqlServerCe;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;

namespace SGQ.Controller
{
    class ControllerDepartamento
    {
		public ControllerDepartamento() { 
            conn = new ConnectFactory();
        }

        private ConnectFactory conn;
        public const string strDelete = "Delete Departamento where Codigo = (@Codigo)";
        public const string strInsert = "Insert into Departamento (Descricao) values (@Descricao)";
        public const string strSelect = "Select Codigo,Descricao From Departamento order by Codigo";
        public const string strSelectDpto = "Select Codigo,Descricao From Departamento Where upper(Descricao) like upper(@Descricao)  order by Codigo";
        public const string strSelectCod = "Select Codigo,Descricao From Departamento Where Codigo = (@Codigo) order by Codigo";
        public const string strUpdate = "Update Departamento set Descricao = (@Descricao) Where Codigo = (@Codigo)";
        private ConnectFactory objConexao;

        public bool Insert(Departamento dpto) 
        {
            try
            {
                using (objConexao = new ConnectFactory())
                {
                    using (SqlCeCommand objCommand = new SqlCeCommand(strInsert, objConexao.CreateConnection()))
                    {
                        objCommand.Parameters.AddWithValue("@Descricao", dpto.Descricao);
                        int lines = objCommand.ExecuteNonQuery();
                        if (lines > 0)
                        {
                            return true;
                        }
                        else {
                            return false;
                        }
                    }
                }
            }catch (Exception e)
            {
                throw new Exception(e.Message,e);
            }
        }
		
		public bool Update(Departamento dpto) 
        {
            try
            {
                using (objConexao = new ConnectFactory())
                {
                    using (SqlCeCommand objCommand = new SqlCeCommand(strUpdate, objConexao.CreateConnection()))
                    {
                        objCommand.Parameters.AddWithValue("@Descricao", dpto.Descricao);
						objCommand.Parameters.AddWithValue("@Codigo", dpto.Codigo);
                        int lines = objCommand.ExecuteNonQuery();
                        if (lines > 0)
                        {
                            return true;
                        }
                        else {
                            return false;
                        }
                    }
                }
            }catch (Exception e)
            {
                throw new Exception(e.Message,e);
            }
        }

		public bool Delete(Departamento dpto) 
        {
            try
            {
                using (objConexao = new ConnectFactory())
                {
                    using (SqlCeCommand objCommand = new SqlCeCommand(strDelete, objConexao.CreateConnection()))
                    {
						objCommand.Parameters.AddWithValue("@Codigo", dpto.Codigo);
                        int lines = objCommand.ExecuteNonQuery();
                        if (lines > 0)
                        {
                            return true;
                        }
                        else {
                            return false;
                        }
                    }
                }
            }catch (Exception e)
            {
                throw new Exception(e.Message,e);
            }
        }

		public List<Departamento> List() 
        {
            try
            {
                using (objConexao = new ConnectFactory())
                {
                    using (SqlCeCommand objCommand = new SqlCeCommand(strSelect, objConexao.CreateConnection()))
                    {
                        SqlCeDataReader dataReader = objCommand.ExecuteReader();
                        List<Departamento> departamentos = new List<Departamento>();
                        Departamento dpto = new Departamento();
                        while (dataReader.Read())
                        {
                            dpto.Codigo = int.Parse(dataReader.GetValue(0).ToString());
                            dpto.Descricao = dataReader.GetValue(1).ToString();
                            departamentos.Add(dpto);
                        }
                        return (departamentos);
                        
                    }
                }
            }catch (Exception e)
            {
                throw new Exception(e.Message,e);
            }
        }

		public DataSet ListDataSet() 
        {
            try
            {
                using (objConexao = new ConnectFactory())
                {
                    using (SqlCeCommand objCommand = new SqlCeCommand(strSelect, objConexao.CreateConnection()))
                    {
                        SqlCeDataAdapter adapter = new SqlCeDataAdapter(objCommand);
						DataSet dataSet = new DataSet();
						adapter.Fill(dataSet);
						return (dataSet);   
                    }
                }
            }catch (Exception e)
            {
                throw new Exception(e.Message,e);
            }
        }

        public DataSet ListDataSet(Departamento dpto)
        {
            if (dpto.Descricao.Length > 0)
            {
                try
                {
                    using (objConexao = new ConnectFactory())
                    {
                        using (SqlCeCommand objCommand = new SqlCeCommand(strSelectDpto, objConexao.CreateConnection()))
                        {

                            objCommand.Parameters.AddWithValue("@Descricao", "%" + dpto.Descricao + "%");
                            SqlCeDataAdapter adapter = new SqlCeDataAdapter(objCommand);
                            DataSet dataSet = new DataSet();
                            adapter.Fill(dataSet);
                            return (dataSet);
                        }
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message, e);
                }

            }
            else if (null != dpto.Codigo)
            {
                try
                {
                    using (objConexao = new ConnectFactory())
                    {
                        using (SqlCeCommand objCommand = new SqlCeCommand(strSelectCod, objConexao.CreateConnection()))
                        {

                            objCommand.Parameters.AddWithValue("@Codigo", dpto.Codigo);
                            SqlCeDataAdapter adapter = new SqlCeDataAdapter(objCommand);
                            DataSet dataSet = new DataSet();
                            adapter.Fill(dataSet);
                            return (dataSet);
                        }
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message, e);
                }
            }
            else {
                return ListDataSet(dpto);
            }
        }
    }
}
